<?php
/*
 * @Author: your name
 * @Date: 2021-11-03 15:16:15
 * @LastEditTime: 2021-12-22 16:26:32
 * @LastEditors: Please set LastEditors
 * @Description: 打开koroFileHeader查看配置 进行设置: https://github.com/OBKoro1/koro1FileHeader/wiki/%E9%85%8D%E7%BD%AE
 * @FilePath: \property\app\Models\admin_model\Column.php
 */

namespace App\Models\admin_model;

use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;

class TowerBuilding extends Model
{
    use HasFactory;
    static string $tableName = 'tower_building';

    public static function list($whereStr = '', $limitStr = '',$field='*')
    {
        return DB::select(' SELECT '.$field.' FROM ' . self::$tableName . '   WHERE  `delete_time` is null ' . $whereStr . ' ORDER BY `top_time` DESC,`sort` DESC , `id` DESC  ' . $limitStr);
    }
    
    public static function count($whereStr = '')
    {
        return DB::select(' SELECT COUNT(*) as `count` FROM ' . self::$tableName . '   WHERE  `delete_time` is null ' . $whereStr );
    }

    public static function buildingSelectList(){
        // echo ' SELECT `id` as `value`,`title`  FROM '.self::$tableName.' WHERE `delete_time` is null AND `shelves` = 1 ORDER BY `top_time` DESC,`sort` DESC `id` DESC ';
        return DB::select(' SELECT `id` as `value`,`title`  FROM '.self::$tableName.' WHERE `delete_time` is null AND `shelves` = 1 ORDER BY `top_time` DESC,`sort` DESC, `id` DESC ');
    }
   
    public static function buildingWebSelOneContent($id){
        // echo " SELECT `building`.*, avg(`housing`.`meters_money`) as `avgPrice`,COUNT(*) as `InrentNum` FROM `tower_building` as `building` LEFT  JOIN `housing` ON `housing`.`building_id` = `building`.`id` WHERE `housing`.`delete_time` is null  AND `housing`.`shelves` = 1 AND `building`.`id` = ".$id." GROUP BY `building`.`id`  "
        return DB::select(" SELECT `building`.*, avg(`housing`.`meters_money`) as `avgPrice`,COUNT(*) as `InrentNum` FROM `tower_building` as `building` LEFT  JOIN `housing` ON `housing`.`building_id` = `building`.`id` WHERE `housing`.`delete_time` is null  AND `housing`.`shelves` = 1 AND `building`.`id` = ".$id." GROUP BY `building`.`id`  ");
      }

      public static function buildingWebSelOne($id){
        //   echo " SELECT `building`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,COUNT(*) as `count`,MAX(`housing`.`spaceNum`) as `maxSpaceNum`,MIN(`housing`.`spaceNum`) as `minSpaceNum`,MAX(`housing`.`meters_money`) as `maxMeters_money`,MIN(`housing`.`meters_money`) as `minMeters_money` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE `housing`.`delete_time` is null AND `housing`.`shelves` = 1 AND `building`.`id` = ".$id." GROUP BY `building`.`id` LIMIT 1 ";
        //  echo " SELECT `building`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,COUNT(*) as `count`,MAX(`housing`.`spaceNum`) as `maxSpaceNum`,MIN(`housing`.`spaceNum`) as `minSpaceNum`,MAX(`housing`.`meters_money`) as `maxMeters_money`,MIN(`housing`.`meters_money`) as `minMeters_money`, sum(CASE WHEN `housing`.`spaceNum` <= 100 THEN 1 ELSE 0 END ) as `100_part`,sum(CASE WHEN `housing`.`spaceNum` > 100  AND  `housing`.`spaceNum` <= 200 THEN 1 ELSE 0 END ) as `200_part`,sum(CASE WHEN `housing`.`spaceNum` > 200 AND `housing`.`spaceNum` <= 300 THEN 1 ELSE 0 END ) as `300_part`, sum( CASE WHEN `housing`.`spaceNum` > 300 AND `housing`.`spaceNum` <=  500 THEN 1 ELSE 0 END ) as `500_part`, sum( CASE WHEN `housing`.`spaceNum` > 500 and `housing`.`spaceNum` <= 1000 THEN 1 ELSE 0 END ) as `1000_part`,sum( CASE WHEN   `housing`.`spaceNum` > 1000 THEN 1 ELSE 0 END  ) as `1100_part` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE `housing`.`delete_time` is null AND `housing`.`shelves` = 1 AND `building`.`id` = ".$id." GROUP BY `building`.`id` LIMIT 1 ";
        return DB::select(" SELECT `building`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,IFNULL(MAX(`housing`.`spaceNum`),0) as `maxSpaceNum`,IFNULL(MIN(`housing`.`spaceNum`),0) as `minSpaceNum`,IFNULL(MAX(`housing`.`meters_money`),0) as `maxMeters_money`,IFNULL(MIN(`housing`.`meters_money`),0) as `minMeters_money`,sum( CASE WHEN `housing`.`spaceNum` is not null  THEN 1 ELSE 0 END ) AS `count`, sum(CASE WHEN `housing`.`spaceNum` <= 100 THEN 1 ELSE 0 END ) as `100_part`,sum(CASE WHEN `housing`.`spaceNum` > 100  AND  `housing`.`spaceNum` <= 200 THEN 1 ELSE 0 END ) as `200_part`,sum(CASE WHEN `housing`.`spaceNum` > 200 AND `housing`.`spaceNum` <= 300 THEN 1 ELSE 0 END ) as `300_part`, sum( CASE WHEN `housing`.`spaceNum` > 300 AND `housing`.`spaceNum` <=  500 THEN 1 ELSE 0 END ) as `500_part`, sum( CASE WHEN `housing`.`spaceNum` > 500 and `housing`.`spaceNum` <= 1000 THEN 1 ELSE 0 END ) as `1000_part`,sum( CASE WHEN   `housing`.`spaceNum` > 1000 THEN 1 ELSE 0 END  ) as `1100_part` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` AND `housing`.`delete_time` is null AND `housing`.`shelves` = 1 LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE   `building`.`id` = ".$id." GROUP BY `building`.`id` LIMIT 1 ");

      }

      public static function buildingWebSelOneUser($id,$openid){
        //   echo " SELECT `building`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,COUNT(*) as `count`,MAX(`housing`.`spaceNum`) as `maxSpaceNum`,MIN(`housing`.`spaceNum`) as `minSpaceNum`,MAX(`housing`.`meters_money`) as `maxMeters_money`,MIN(`housing`.`meters_money`) as `minMeters_money` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE `housing`.`delete_time` is null AND `housing`.`shelves` = 1 AND `building`.`id` = ".$id." GROUP BY `building`.`id` LIMIT 1 ";
        //  echo " SELECT `building`.*,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,COUNT(*) as `count`,MAX(`housing`.`spaceNum`) as `maxSpaceNum`,MIN(`housing`.`spaceNum`) as `minSpaceNum`,MAX(`housing`.`meters_money`) as `maxMeters_money`,MIN(`housing`.`meters_money`) as `minMeters_money`, sum(CASE WHEN `housing`.`spaceNum` <= 100 THEN 1 ELSE 0 END ) as `100_part`,sum(CASE WHEN `housing`.`spaceNum` > 100  AND  `housing`.`spaceNum` <= 200 THEN 1 ELSE 0 END ) as `200_part`,sum(CASE WHEN `housing`.`spaceNum` > 200 AND `housing`.`spaceNum` <= 300 THEN 1 ELSE 0 END ) as `300_part`, sum( CASE WHEN `housing`.`spaceNum` > 300 AND `housing`.`spaceNum` <=  500 THEN 1 ELSE 0 END ) as `500_part`, sum( CASE WHEN `housing`.`spaceNum` > 500 and `housing`.`spaceNum` <= 1000 THEN 1 ELSE 0 END ) as `1000_part`,sum( CASE WHEN   `housing`.`spaceNum` > 1000 THEN 1 ELSE 0 END  ) as `1100_part` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id`   WHERE `housing`.`delete_time` is null AND `housing`.`shelves` = 1 AND `building`.`id` = ".$id." GROUP BY `building`.`id` LIMIT 1 ";
        return DB::select(" SELECT `building`.*, CASE WHEN `collection`.`id` IS NOT NULL THEN 1 ELSE 0 END as `collection`,`areaCommunity`.`name` as `community_name`,`areaStreet`.`name` as `street_name`,IFNULL(MAX(`housing`.`spaceNum`),0) as `maxSpaceNum`,IFNULL(MIN(`housing`.`spaceNum`),0) as `minSpaceNum`,IFNULL(MAX(`housing`.`meters_money`),0) as `maxMeters_money`,IFNULL(MIN(`housing`.`meters_money`),0) as `minMeters_money`,sum( CASE WHEN `housing`.`spaceNum` is not null  THEN 1 ELSE 0 END ) AS `count`, sum(CASE WHEN `housing`.`spaceNum` <= 100 THEN 1 ELSE 0 END ) as `100_part`,sum(CASE WHEN `housing`.`spaceNum` > 100  AND  `housing`.`spaceNum` <= 200 THEN 1 ELSE 0 END ) as `200_part`,sum(CASE WHEN `housing`.`spaceNum` > 200 AND `housing`.`spaceNum` <= 300 THEN 1 ELSE 0 END ) as `300_part`, sum( CASE WHEN `housing`.`spaceNum` > 300 AND `housing`.`spaceNum` <=  500 THEN 1 ELSE 0 END ) as `500_part`, sum( CASE WHEN `housing`.`spaceNum` > 500 and `housing`.`spaceNum` <= 1000 THEN 1 ELSE 0 END ) as `1000_part`,sum( CASE WHEN   `housing`.`spaceNum` > 1000 THEN 1 ELSE 0 END  ) as `1100_part` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` AND `housing`.`delete_time` is null AND `housing`.`shelves` = 1 LEFT JOIN `area` as `areaStreet` ON `areaStreet`.`id` = `building`.`street_id` LEFT JOIN  `area` as `areaCommunity` ON `areaCommunity`.`id` =  `building`.`community_id` LEFT JOIN `find_shop_collection_history` as `collection` ON `collection`.`user_openid` = '".$openid."' AND `collection`.`table` = 'building' AND `collection`.`type` = 2 AND `collection`.`target_id` = `building`.`id`   WHERE   `building`.`id` = ".$id." GROUP BY `building`.`id` LIMIT 1 ");

      }

      public static function buildingWebListRand($id,$limit){
          return DB::select(" SELECT `building`.`id`,`building`.title,`building`.`latitude`,`building`.`longitude`,`building`.`cover_image`,IFNULL(avg(`housing`.`meters_money`),0) as `avgMeters_money` FROM `tower_building` as `building` LEFT JOIN `housing` ON `housing`.`building_id` = `building`.`id` WHERE `housing`.`delete_time` is null AND  (`housing`.`shelves` = 1 OR `housing`.`shelves` IS NULL) AND `housing`.`delete_time` is null AND  `building`.`id` !=  $id AND `building`.`shelves` = 1 AND `building`.`delete_time` is null  GROUP BY `building`.`id` ORDER BY rand()  LIMIT 0 ,".$limit);
      }

    
}
